package com.pps.sdk.utils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.pps.sdk.model.DataLog;
import com.pps.sdk.model.PaymentLog;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.text.TextUtils;

public class SqliteHandler {
	private final static String DATALOGTABLE = "PPSLOG";
	private final static String PAYMENTLOGTABLE = "PPSPAYMENT";
	private static String LOG_SQLITE_PATH = "";

	private static boolean sdIsExist = true;

	static {
		sdIsExist = Environment.getExternalStorageState().equals(
				android.os.Environment.MEDIA_MOUNTED); // 判断sd卡是否存在;

		if (sdIsExist) {
			File sdDir = Environment.getExternalStorageDirectory();// 获取跟目录
			sdDir = new File(sdDir.getPath() + "/ppsgame");

			if (!sdDir.exists()) {
				sdDir.mkdirs();
			}

			String sqlitePath = sdDir.getPath() + "/ppslog.db";
			File sqliteFile = new File(sqlitePath);

			try {
				if (!sqliteFile.exists()) {
					sqliteFile.createNewFile();

					String log = "CREATE TABLE \"PPSLOG\" (\"_id\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , " +
							"\"gameid\" VARCHAR NOT NULL , \"data_url\" VARCHAR NOT NULL, \"action\" VARCHAR NOT NULL, " +
							"\"status\" VARCHAR NOT NULL , \"post_time\" VARCHAR )";
					
					String payment = "CREATE TABLE \"PPSPAYMENT\" (\"_id\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , " +
							"\"gameid\" VARCHAR NOT NULL , \"data_url\" VARCHAR NOT NULL, \"channel\" VARCHAR NOT NULL, \"payment_time\" VARCHAR, \"mapping\" VARCHAR, \"serverid\" VARCHAR NOT NULL)";
					SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(
							sqliteFile, null);
					mysql.execSQL(log);
					mysql.execSQL(payment);
					mysql.close();
				}else{
					SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(
							sqliteFile, null);
					String payment = "CREATE TABLE IF NOT EXISTS \"PPSPAYMENT\" (\"_id\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
							" \"gameid\" VARCHAR NOT NULL, \"data_url\" VARCHAR NOT NULL, \"channel\" VARCHAR NOT NULL, \"payment_time\" VARCHAR, \"mapping\" VARCHAR, \"serverid\" VARCHAR NOT NULL)";
					mysql.execSQL(payment);
					mysql.close();
				}

				LOG_SQLITE_PATH = sqliteFile.getPath();

			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 查询数据投递日志
	 * @param gameid
	 * @param limit
	 * @param offset
	 * @return
	 */
	public static List<DataLog> getDataLogList(String gameid){
		if (!sdIsExist) {
			return null;
		}
		
		if(TextUtils.isEmpty(LOG_SQLITE_PATH)){
			
			return null;
		}
		
		File sqliteFile = new File(LOG_SQLITE_PATH);
		SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(
				sqliteFile, null);
		String[] columns = new String[]{"_id","gameid","data_url","action","status","post_time"};
		String selection = null;
		String[] selectionArgs = null;
		if(!TextUtils.isEmpty(gameid)){
			selection = "gameid = ?";
			selectionArgs = new String[]{gameid};
		}
//		String limitString = limit + " Offset " + offset; //09-17 16:42:38.871: E/AndroidRuntime(30512): Caused by: java.lang.IllegalArgumentException: invalid LIMIT clauses:Limit 10 Offset 0
		
		Cursor cursor = mysql.query(DATALOGTABLE, columns, selection, selectionArgs, null, null, "_id desc");
		
		List<DataLog> result = new ArrayList<DataLog>();
		if(cursor != null){
			while(cursor.moveToNext()){
				DataLog log = new DataLog();
				log.gameid = cursor.getString(cursor.getColumnIndex("gameid"));
				log.action = cursor.getString(cursor.getColumnIndex("action"));
				log.dataUrl = cursor.getString(cursor.getColumnIndex("data_url"));
				log.status = cursor.getString(cursor.getColumnIndex("status"));
				log.postTime = cursor.getString(cursor.getColumnIndex("post_time"));
				result.add(log);
			}
			
			cursor.close();
		}
		
		mysql.close();
		
		return result.size() == 0 ? null : result;
	}
	
	/**
	 * 删除数据投递的日志
	 * @param gameId
	 */
	public static void deleteDataLog(String gameId){
		if (!sdIsExist) {
			return ;
		}
		
		if(TextUtils.isEmpty(LOG_SQLITE_PATH)){
			
			return ;
		}
		
		File sqliteFile = new File(LOG_SQLITE_PATH);
		SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(
				sqliteFile, null);
		String whereClause = null;
		String[] whereArgs = null;
		if(!TextUtils.isEmpty(gameId)){
			whereClause = "gameid = ?";
			whereArgs = new String[]{gameId};
		}
		
		mysql.delete(DATALOGTABLE, whereClause, whereArgs);
		mysql.close();
	}
	
	/**
	 * 查询充值日志
	 * @param gameid
	 * @return
	 */
	public static List<PaymentLog> getPaymentLogList(String gameid){
		if (!sdIsExist) {
			return null;
		}
		
		if(TextUtils.isEmpty(LOG_SQLITE_PATH)){
			
			return null;
		}
		
		File sqliteFile = new File(LOG_SQLITE_PATH);
		SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(
				sqliteFile, null);
		String[] columns = new String[]{"_id","gameid","data_url","channel","payment_time","mapping","serverid"};
		String selection = null;
		String[] selectionArgs = null;
		if(!TextUtils.isEmpty(gameid)){
			selection = "gameid = ?";
			selectionArgs = new String[]{gameid};
		}
//		String limitString = limit + " Offset " + offset; //09-17 16:42:38.871: E/AndroidRuntime(30512): Caused by: java.lang.IllegalArgumentException: invalid LIMIT clauses:Limit 10 Offset 0
		
		Cursor cursor = mysql.query(PAYMENTLOGTABLE, columns, selection, selectionArgs, null, null, "_id desc");
		
		List<PaymentLog> result = new ArrayList<PaymentLog>();
		if(cursor != null){
			while(cursor.moveToNext()){
				PaymentLog log = new PaymentLog();
				log.gameid = cursor.getString(cursor.getColumnIndex("gameid"));
				log.dataUrl = cursor.getString(cursor.getColumnIndex("data_url"));
				log.channel = cursor.getString(cursor.getColumnIndex("channel"));
				log.paymentTime = cursor.getString(cursor.getColumnIndex("payment_time"));
				log.mapping = cursor.getString(cursor.getColumnIndex("mapping"));
				log.serverid = cursor.getString(cursor.getColumnIndex("serverid"));
				result.add(log);
			}
			
			cursor.close();
		}
		
		mysql.close();
		
		return result.size() == 0 ? null : result;
	}
	
	/**
	 * 删除充值的日志
	 * @param gameId
	 */
	public static void deletePaymentLog(String gameId){
		if (!sdIsExist) {
			return ;
		}
		
		if(TextUtils.isEmpty(LOG_SQLITE_PATH)){
			
			return ;
		}
		
		File sqliteFile = new File(LOG_SQLITE_PATH);
		SQLiteDatabase mysql = SQLiteDatabase.openOrCreateDatabase(
				sqliteFile, null);
		String whereClause = null;
		String[] whereArgs = null;
		if(!TextUtils.isEmpty(gameId)){
			whereClause = "gameid = ?";
			whereArgs = new String[]{gameId};
		}
		
		mysql.delete(PAYMENTLOGTABLE, whereClause, whereArgs);
		mysql.close();
	}
}
